﻿-- =============================================
-- Author:		<Author,XFL>
-- Create date: <Create Date,2011 12 21>
-- Description:	<Description,用于调整后的查询登录账号信息,包含统计数据>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Users_GetList_New20111221] 
	(
	@CompanyId int,
	@SelType int,--用于判断查询条件 级锁定状态
	@Stext nvarchar(50),
	@IsDelete bit,
	@StartIndex int,
	@EndIndex int
	,@RolesId varchar(10)
	)
AS



	BEGIN

--		Declare @RolesId varchar(20)
--		set @RolesId='0'

		SET NOCOUNT ON;
		;WITH list As(Select ROW_NUMBER() OVER (ORDER BY UserName) AS Row,
		UserId,
		UserName,
		Password,
		RTX,
		Email,
		CompanyId,
		ISNULL([DepartmentId],0) AS[DepartmentId], --- 部门ID
		[Name],
		CreateDate,
		IsLockout,
		ISNULL(LastLoginDate,'1900-01-01') As LastLoginDate,
		FailedPasswordAttemptCount,
		ISNULL([UserInRoles],'--') AS [UserInRoles] ---权限参数

--计算总数
		--(Select Count(UserId) From aspnet_Users Where (IsDelete=@IsDelete) And (@SelType=2 Or IsLockout=@SelType) and (@CompanyId=0 Or CompanyId=@CompanyId) And (@Stext IS NULL Or [Name] like '%'+@Stext+'%' Or [UserName] like '%'+@Stext+'%')) AS Count_Sum
		,rootid
		From aspnet_Users WHERE UserName<>'admin' and (IsDelete=@IsDelete) And (@SelType=2 Or IsLockout=@SelType) and (@CompanyId=0 Or CompanyId=@CompanyId) And (@Stext IS NULL Or [Name] like '%'+@Stext+'%' Or [UserName] like '%'+@Stext+'%') and (@RolesId='0' or  [UserInRoles] like '%[[]'+@RolesId+']%' )

		)
		
		Select *,(select count(0) from list ) AS Count_Sum  From list Where Row between @StartIndex and @EndIndex
		RETURN
		
	END
